from sqlalchemy import Column, Integer, String, Text, Table, ForeignKey
from sqlalchemy.orm import relationship
from app.core.database import Base


# 中间表（多对多关联）
character_languages = Table(
    "character_languages",
    Base.metadata,
    Column("character_id", String(100), ForeignKey("characters.id", ondelete="CASCADE"), primary_key=True),
    Column("language_id", Integer, ForeignKey("languages.id", ondelete="CASCADE"), primary_key=True),
)

character_personalities = Table(
    "character_personalities",
    Base.metadata,
    Column("character_id", String(100), ForeignKey("characters.id", ondelete="CASCADE"), primary_key=True),
    Column("personality_id", Integer, ForeignKey("personalities.id", ondelete="CASCADE"), primary_key=True),
)

character_skills = Table(
    "character_skills",
    Base.metadata,
    Column("character_id", String(100), ForeignKey("characters.id", ondelete="CASCADE"), primary_key=True),
    Column("skill_id", Integer, ForeignKey("skills.id", ondelete="CASCADE"), primary_key=True),
)


class Language(Base):
    __tablename__ = "languages"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), unique=True, nullable=False)

    characters = relationship(
        "Character",
        secondary=character_languages,
        back_populates="languages_rel",
    )


class Personality(Base):
    __tablename__ = "personalities"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(64), unique=True, nullable=False)

    characters = relationship(
        "Character",
        secondary=character_personalities,
        back_populates="personalities_rel",
    )


class Skill(Base):
    __tablename__ = "skills"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), unique=True, nullable=False)
    description = Column(Text, nullable=True)

    characters = relationship(
        "Character",
        secondary=character_skills,
        back_populates="skills_rel",
    )



